package org.schoolnavigator.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.schoolnavigator.db.DBManager;
import org.schoolnavigator.to.EmployeeTO;


public class EmployeeDAO {
	private int noOfRecords;
	
	public List<EmployeeTO> viewAllEmp(int offset, int noOfRec) throws SQLException {
		System.out.println("===== inside EmployeeDAO ===== ");
		Connection con = null;
		PreparedStatement pstRecords = null;
		Statement stRows = null;
		ResultSet rsRec = null;
		ResultSet rsRows = null;
		EmployeeTO emp = null;
		List<EmployeeTO> empList = new ArrayList<EmployeeTO>();
		String qryRecords = "SELECT SQL_CALC_FOUND_ROWS * FROM employee limit ?, ? ";
		
		try{
			//con = ConnectionFactory.getInstance().getConnection();
			con = DBManager.getInstance().getConnection();
			pstRecords = con.prepareStatement(qryRecords);
			pstRecords.setInt(1, offset);
			pstRecords.setInt(2, noOfRec);
			rsRec = pstRecords.executeQuery();
			while(rsRec.next()){
				emp = new EmployeeTO();
				emp.setId(rsRec.getInt("id"));
				emp.setName(rsRec.getString("name"));
				emp.setSalary(rsRec.getInt("salary"));
				empList.add(emp);
			}
			//get no of records
			stRows = con.createStatement();
			rsRows = stRows.executeQuery("SELECT FOUND_ROWS()");
			if(rsRows.next()){
				noOfRecords = rsRows.getInt(1);
				System.out.println("noOfRecords == " + noOfRecords);
			}
			
			
		}catch(SQLException sqle){
			sqle.printStackTrace();
		}finally{
			try{
				if(rsRows != null)
					rsRows.close();
				if(rsRec != null)
					rsRec.close();
				if(pstRecords != null)
					pstRecords.close();
				if(stRows != null)
					stRows.close();
				if(con != null)
					con.close();
						
			}catch(SQLException sqle){
				sqle.printStackTrace();
			}
		}
		
		return empList;
		
	}
	
	public int getNoOfRecords(){
		return noOfRecords;
	}

}
